Pandas in a nutshell

Pandas is the Python library designed specifically for data analysis. The author of Python for Data Analysis, Wes McKinney, began developing Pandas in 2008

while at AQR Capital Management out of need for a performant, flexible tool to perform quantitative analysis on financial data. Before leaving AQR he was able to convince management to allow him to open source the library.

Another AQR employee, Chang She, joined the effort in 2012 as the second major contributor to the library. Right around that time, the library became popular in the Python community, and many more contributors joined the project making it one of the most vital and active data analysis libraries for Python. (Wikipedia )

Pandas can be thought of the Python equivalent of Microsoft Excel. It abstracts the notion of the spreadsheet, allowing the user to use powerful and robust analytical tools generally to automate repeated processes.

The twin centerpieces of the Pandas library are the Series and the DataFrame. The Series class is, at its core, a one-dimensional NumPy array, surrounded by additional information, such as its index. The DataFrame is conceptually an array of Series classes, each sharing the same index.

We will be using Wes McKinney's GitHub notebook as a skeleton. He imports the following libraries for later use:

Introduction to pandas data structures

Series

Consider the following input:

We have set the variable obj to reference a new Pandas Series, which we initialized by giving a Python list as input. Notice that Pandas automatically interprets the input data as type int64, which indicates that it is fairly smart! Also, notice that upon printing obj we see two columns. The first column is the index of the Series class, which is presently the natural index, range(4). The second column is the input data that we gave initially, which Pandas refers to as the values of obj. You can access these columns individualy by calling obj.index and obj.values, respectively. For example:

Indexing

As previously mentioned, the natural index simply starts at 0 and increments integers to the size of the list of the input values. Alternatively, we can specify the index explicitly when we initialize the Series, as in the following:

What happens when you examine the index now?

Don't be alarmed by the u prefix to each of the index values. In Python (as well as in other languages, this simply indicates a Unicode string. Ostensibly, there is no difference between normal strings and Unicode strings.

You can access a particular member of the Series data by specifying its index. For example,

This allows you to change the value of specific entries in your Series data. Additionally, you can call subSeries by specifying a sublist of the index.

A powerful tool in Pandas is the ability to concisely access data meeting Boolean qualifications. In the case below, obj2 > 0 is given as the "index," and the output is the subSeries of obj2 for which all entries are positive.

Aside

What is obj2 > 0 actually?

This is actually a neat property of Pandas which is similar to NumPy. In NumPy, suppose you are given an array:

The actual array itself is given by

The Boolean array specifying which elements of arr are positive is given by

Similarly, you can generate a new Series of Boolean values by subjecting the original Series to a Boolean statement, as we did above.

Broadcasting

Like NumPy, we can broadcast arithmetic operations onto Series data. For example,

returns a Series whose values are doubled, and

returns a Series whose values have all been subject to the transformation $x\mapsto e^x$. Notice additionally that the dtype of obj2 has automatically been changed from int64 to float64. Again, Pandas is being smart!

Querying a Series

In Python, there is a binary operator called in, which takes two "arguments." The left-hand argument is can be any type of data (or object, we won't get into this), while the right-hand argument is some type of iterable object. Then in returns True if the left-hand argument is an element of the right-hand argument. Mathematically, this is equivalent to set membership. For example,

is equivalent to noting that if $$\text{Odds} = \{ n : 0 \leq n < 20 \text{ and } n \text{ is odd}\}$$ we have that $$3 \in \text{Odds}$$ while $$2 \notin \text{Odds}.$$

(In fact, we have already seen in in action with Python's for loop, which has the form

for element in iterative_object:

indicating that the code should loop through every element element that is a member of iterative_object.)

You can use in with Pandas Series to test that an element is a member of the index of the Series. For example,

Aside

We have talked about the native Python list data type. There is another important native data type in Python, called a dict, which you can learn about more here. Python dict types are similar to association lists in Scheme, in that they require a lookup key in order to access elements.

Crucially, Pandas can create a Series from a dict by interpreting the key for each item as its corresponding index value, which is actually quite natural. In this sense, I find that it is useful to think of the relationship between NumPy and Pandas as akin to the relationship between a list and a dict.

What happens when you use an existing dataset with a new index, in which there is a new, unfilled index?

In this case, California is a previously-unused index, which has no corresponding value. Thus, Pandas initializes the new Series with the value corresponding to California set to NaN (Python-speak for null).

The isnull method returns a Series of Boolean values whenever the original Series has a null (NaN) value.

The notnull method does the exact opposite!

The methods isnull and notnull are "static" in the sense that they can be called straight from the pd module or for a specific Series object.

Recall the two Series, obj3 and obj4:

Arithmetic operations between distinct Series objects work conservatively. For data types, int64 + float64 = float64 to preserve the decimal information. The summed index is the union of the two indices. Consider the following example:

No entry for California exists in obj3, while no entry for Utah exists in obj4. Pandas interprets NaN + x = NaN for all x, so the resultant Series sets NaN for both California and Utah.

We can set some metadata for a Series, such as the name of the values column and the name of the index column.

You can also completely change the index at any time. This is something we will get into more detail later.

DataFrame

Like we said before, you can think of a DataFrame as an array of Series objects. Specifically, a DataFrame is a two-dimensional array of Series objects, all indexed by the same index series. You can also think of a DataFrame as a single Microsoft Excel spreadsheet.

One way to initialize a DataFrame is by giving a dict where each key indicates a Python list.

You can reorder the columns in a new DataFrame using the following argument:

Similarly, the index optional argument in DataFrame allows you to specify the index list. Additionally, adding a debt column with no corresponding data in data will initialize a column filled with NaN entries.

You can access the columns of a DataFrame as follows:

You can slice a particular column by specifying its column name. Notice how this returns a Series.

Alternatively, you can slice a column using the following syntax:

To slice a row, you can specify an index, which will return a Series representing the row at the index.

Broadcasting works in the natural way that you might expect:

You can also give a particular column a list or ndarray, which will then be distributed across the column.

Finally, you can give a column of a DataFrame a Series. If you specify a Series with an index differing from the main DataFrame, then the entries of the DataFrame will be set to NaN.

The point of Pandas is there are numerous ways to achieve the same effect, depending on whatever is easiest for the task at hand. Here is another way to add a column:

We can also use Python's del function to remove a column:

One final way to initialize DataFrame objects is with nested dict objects.

You can transpose a DataFrame if it makes more sense to work with the rows and columns flipped.

You can do this transpose operation from the outset by manually specifying the index.

DataFrame objects can also be initialized from dicts of Series objects.

If you need to access the underlying ndarray from any DataFrame, use the DataFrame.values field.

Index objects

The Index is the "metadata" object for Series and DataFrame objects. We've seen ways of initializing Index objects before, so we will go over some features of these objects.

Index objects can be sliced like arrays.

Importantly, Index objects are not mutable, so you can't change their values in the natural way:

You can initialize Index objects with NumPy ndarray objects.

Essential functionality

Now that we are familiar with the basic objects in Pandas, we will start working with the mechanics of these objects.

Reindexing

In the previous section we mentioned that Index objects are immutable. Here we will address this issue.

The simplest way to change an Index object in an existing Series or DataFrame is with the reindex method.

In the above example, since "e" was not in the original Index, the corresponding Series value is set to NaN. If you want to change the default fill value, reindex can take an additional parameter, fill_value.

A different approach uses a method parameter that attempts to extrapolate existing data into the new Index. One such method is ffill, which "step-fills" the existing data forward. Alternatively, bfill "step-fills" the data backwards.

The reindex method works for DataFrame objects as well. For DataFrame objects, reindex can also specify column reindexing.

Alternatively, you can use ix to achieve the same effect more concisely.

Dropping entries from an axis

Suppose you have a Series object with data you wish to remove. Using the drop method, you can specify an index element to remove.

You can also drop a list of index elements at once.

The same works for DataFrame objects and the drop method.

Additionally, DataFrame.drop() can remove columns by specifying an axis parameter.

Indexing, selection, and filtering

In this section we will explore the various techniques available for slicing Series and DataFrame objects. One the one hand, we can deal with these objects as dict structures, accessing elements by requesting their index keys. On the other hand, we can treat these objects as list structures, accessing elements by the order of the index list.

This flexibility allows you to incorporate all of the previous array slicing that worked for NumPy ndarray objects.

Conversely, you can use a list of dict keys to achieve the same end.

Here are some alternative slicing techniques for Series objects.

You can assign values to sub-objects which then reflect on the original object.

The same capabilities are extended to the DataFrame objects. The added flexibility is that the same indexing techniques also apply to column slicing as well as index slicing.

The natural slicing will always refer to the index list, not the column list, which is useful to keep in mind.

data[:2]

Recall that you can generate a corresponding Boolean array by subjecting a DataFrame to a boolean statement, such as the following:

You can use Boolean arrays to do simple thresholding to your data. You can isolate entries in your data subject to identical Boolean conditions, and manipulate these specific subsets of the data.

The DataFrame.ix field gives you even more powerful ways to slice your data. In general, slicing works by providing two arguments, an index and a column specification, and it will then return that particular subset.

You can overload requests by using a list of index or column elements. Additionally, you may reorder the indices or columns in your subset by permuting the order of the specified elements, so long as they exist in the original DataFrame.

The ix approach is very powerful. See if you can work through the mechanics of the next few examples to see just how versatile slicing with ix actually is.

Arithmetic and data alignment

As we mentioned before, we can do arithmetic on Series and DataFrame objects.

Importantly, arithmetic is only performed on elements sharing an index. If either object has an index value that the other does not, the arithmetic operation is undefined, so the resultant object contains an NaN element.

The same holds for DataFrame arithmetic, except now it requires that both the index and column of each DataFrame object is well-defined.

Arithmetic methods with fill values

Often NaN values are undesirable, as they can cause errors when doing arithmetic operations on the data.

This can be avoided by using the built-in DataFrame.add() method, which takes as parameters a DataFrame object and an optional fill_value which deals with otherwise NaN entries.

In fact, most DataFrame organization methods take fill_value as a parameter to deal with undefined cases, such as reindex.

Operations between DataFrame and Series

Broadcasting NumPy arrays is a very useful technique for performing arithmetic operations concisely. And efficiently, actually. This is because while normal Python arithmetic is interpreted, NumPy arithmetic is based on compiled C code, which is much more efficient in general.

Normally we think of broadcasting a scalar element onto a one-dimensional array vector. In fact, broadcasting is much more powerful, because you can broadcast an array over a bigger array.

DataFrame and Series objects work along similar lines.

You can broadcast the values in a Series over its parent DataFrame as you would with NumPy ndarrays.

Of course, if either a Series and DataFrame object has index or column values the other does not, the undefined arithmetic simply is sent to NaN. (We discussed ways to avoid this issue in the previous sections).

Using the built-in DataFrame arithmetic operations such as add or sub gives the option to specify the axis (0: index, 1: columns) over which the arithmetic will take place (again, you can use fill_value to avoid potential NaN values).

Function application and mapping

One of the most important capabilities of Series and DataFrame is the ability to apply function transformations to the data. Every ufunc defined by NumPy can be applied to a DataFrame (or Series) object.

For example, you can apply a nonnegativity transform by including a built-in NumPy absolute value.

You can define and apply custom functions in two fashions. One is by using lambdas to construct anonymous functions:

Alternatively, you can define your own unary function and simply apply it using the same overall approach.

For presentations and general readability, it is useful to format decimal or date values into condensed forms, and Pandas lets you achieve this by using the applymap method for DataFrame and Series objects. The difference between apply and applymap is rather subtle and often functionally neglibible, but the idea is that apply works on a particular subsets of rows or columns, whereas applymap is element-wise.

Alternatively, you can use the built-in Python map function.

Exercise:

Determine which of apply or map is computationally more efficient.

Sorting and ranking

One fundamental problem in data analysis, let alone computer science in general, is sorting data. Pandas provides a number of techniques for sorting information in the index, columns, and the actual data itself.

The first technique is sort_index, which is a method for both Series and DataFrame objects. For Series objects, sort_index works as follows:

Since there is only one meaningful index to sort, the labels, sort_index is a very intuitive method. I want to point out that sort_index does not have side-effects; that is, calling sort_index on an object does not actually change the internals of the object itself. Instead, a sorted copy of the original object is produced.

The method sort_index works similarly with DataFrame objects, but now there are two potential axes along which to sort. The default is the index, as we see below:

By specifying the axis as a parameter, one can choose the columns instead. (Recall that in Python everything begins at 0, so the second axis corresponds to axis number 1).

The sort_index method also allows you to flip the ordering by specifying the ascending parameter.

If you want to sort the elements themselves, as opposed to the index, Pandas provides the order method for Series objects.

By default, NaN values are placed at the end upon sorting the Series.

For DataFrame objects you can specify the index or column you wish to sort. Additionally, if your data set is properly constructed, you can sort by two columns or indices, as the below example exhibits:

Axis indexes with duplicate values

It is possible for a Series or DataFrame object not to have a unique index. For example:

Pandas has a field for the index of any object to indicate whether or ot the index is unique (no duplicate indices).

If an index is not unique, then slicing the object for a repeated index returns a sub-object. For example:

For unique index items, the default return-type is a scalar:

The same goes for DataFrame objects, although they are more complicated.

Remember that you have to slice the index using ix, and you will observe the same behavior.

Summarizing and computing descriptive statistics

Oftentimes, you need a quick way to come up with basic summary statistics of data sets. The solution that Pandas provides is incredibly robust, especially with regard to NaN entries.

By default, the sum method will skip NaN entries for each column in a DataFrame.

For the DataFrame object, you can also apply along either the index axis or the column axis. Again, sum will skip over NaN elements when arriving at a value.

If you don't want this behavior, you can always tell the statistics function you are applying not to skip the NaN entries. Here is an example using mean:

Another useful statistic is idxmax, which returns the index of the maximum value of a column in a DataFrame.

One incredibly useful method is cumsum, which has a number of important applications in the analysis of probability distributions and random walks.

You can also get a quick overview of all of the summary statistics of a DataFrame simply by calling the describe method.

Descriptive and Summary Statistics

Series objects also have a describe method. The describe method outputs statistics based on the dtype of the underlying object. In the above example, df had a dtype of float64, so describe produced information pertinent to floating-point numerics. In the below example, the Series object has a dtype of object, which results in different summary statistics.

Correlation and covariance

One common problem in data analysis, especially in the analysis of time series data like historical prices for financial securities, is correlation and covariance analysis. To this end Pandas has a number of features to make the analysis simple.

Here is one example, using a built-in data aggregator using Yahoo! Finance in the Pandas API. Returns on a stock are defined as the percent change in the stock's closing value from day-to-day.

When given a Series object, the corr method computes the scalar correlation between the Series and another Series.

By contrast, corr and cov returns a correlation and covariance matrix DataFrame with filled correlation and covariance values, respectively.

The corrwith method computes pairwise correlations and stores the resultant in a Series. Note that the correlation between IBM and IBM is 1.

Passing a DataFrame instead computes correlation with like-columns.

Unique values, value counts, and membership

Given data with repeats, you can eliminate the excess by using the unique method.

The value_counts returns a Series with an index made up of the unique entries in the original Series, and the new entries give the total appearances of each value.

You can perform set-membership operations to, for example, construct masks which you can then apply to your original data.

For DataFrame objects, you can apply the value_counts method to each subseries, producing a new DataFrame of frequency statistics.

Handling missing data

One of the primary problems with data analysis is the prevalence of missing data. In many cases, arithmetic operations, summary statistics, and other functions require that your data be intact in order to provide meaningful results. Pandas gives a number of functions to address the problem of missing data, allowing you to filter it out easily.

Consider this Series of string values.

The isnull method identifies every NaN entry. Alternatively,notnull will identify every non-NaN entry.

Filtering out missing data

A simple way to remove missing entries from a Series object is to use dropna.

Alternatively, you can use Boolean Series and notnull to mask the original data.

DataFrame objects are trickier. For example, how should Pandas handle a mostly-complete row? The correct answer is ambiguous. By default, dropna will eliminate any row with a NaN (we redefined NaN to NA here) value.

Alternatively, you can require that a row be eliminated only if it is completely empty.

You can also specify columns for deletion. Again, you can change the deletion requirements as needed.

The dropna method is very robust. You can also specify a minimum threshold of data in a particular row as a criterion for deletion. In the next example, we threshold at 2 entries per row, allowing rows with one NaN value to stay while deleting any more patchy rows.

Filling in missing data

Instead of eliminating missing data outright, Pandas lets you fill in the missing values. The simple approach, using fillna, is to pass a value that will then replace every NaN entry.

Alternatively, you can specify different fill values in different columns by giving a dict with keys of column names.

Using the inplace argument, you can overwrite the original DataFrame object.

The other main filling technique is to fill by procedure. ffill will copy the previous value in a column into the NaN entry.

In cases where you don't want this to extend indefinitely, you can limit the fill method to a certain number of NaN entries after the last available one.

Hierarchical indexing

From "Python for Data Analysis":

Hierarchical indexing is an important feature of pandas enabling you to have multiple (two or more) index levels on an axis. Somewhat abstractly, it provides a way for you to work with higher dimensional data in a lower dimensional form. Let's start with a simple example; create a Series with a list of lists or arrays as the index:

Notice how the index a corresponds to the sub-indices 1 and 2, and their corresponding data. The index object is thus not a simple list but a series of lists corresponding to the inner sub-indices.

Accessing an outer label will give you the sub-Series that it corresponds to.

You can access sub-indices, which returns the Series of all upper indices and their corresponding values.

You can use unstack to take the multi-index and place it into a DataFrame object.

The inverse of unstack is stack. Observe:

Multi-indexing has a similar logic with DataFrame objects, but it becomes more complicated as both the index and the columns can be given a hierarchy:

For clarity, let's rename the labels so we know what level we are looking at.

Now by specifying any column, whether on the top level or any sublevel, you can get the DataFrame of values corresponding to the name.

MultiIndex objects are independent in Pandas, meaning that you can create them without a corresponding DataFrame and reuse them as needed.

Reordering and sorting levels

You can always swap indices on the same level. For example, if you want key2 and key1 to switch, you can write

Additionally, you can sort a particular index (in general, you can't sort them all). Specify the index by its order (first is 0, second is 2), and you will see the sort take place:

As with the object-oriented paradigm, you can combine these actions into one statement. For example:

Summary statistics by level

With hierarchical indexing, you can specify the level and axis with which to compute summary statistics. If one wants to compute the sum of all values in the key2 index, you get the relevant sub-DataFrame.

This of course gets extended to the columns as well, which you have grown accustomed to with DataFrame methods.

Using a DataFrame's columns

In the examples above we showed how to stack and unstack Series objects into DataFrames. But in general DataFrame objects give you a lot of discretion regarding which columns you want to convert into indices.

You can overload set_index with more than one column to produce a hierarchical index using the values of each respective column.

Crucially, the default Pandas behavior is to remove the indexed columns. You can force Pandas to keep the old columns by specifying the drop parameter: